package com.muniuliuma.sqlite;

import java.util.ArrayList;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteDBHelper extends SQLiteOpenHelper{
	
	private static final String dbName = "test.db";
	
	public static final int errCode = -1;

	private String tableName = null;
	
	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public SQLiteDBHelper(Context context) {
		super(context, dbName, null, 2);
	}

	public SQLiteDBHelper(Context context, String tableName) {
		super(context, dbName, null, 2);
		this.tableName = tableName;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SQLiteTable.bookmark.CREATE_TABLE);
//		db.execSQL(createTable2);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}
	
	public void execSQL(String sql){
		getWritableDatabase().execSQL(sql);
	}
	
	public void insert(String table, String[] fieldValus){
		StringBuffer fieldSql = new StringBuffer();
		for(int i=0; i<fieldValus.length; i++){
			fieldSql.append("'"+fieldValus[i]+"'");
			if(i<fieldValus.length-1){
				fieldSql.append(",");
			}
		}
		String sql = "insert into "+table+" values("+fieldSql.toString()+")";
		getWritableDatabase().execSQL(sql);
	}
	
	public long insert(String table, String[] fieldNames, String[] fieldValus){
		if(fieldNames.length != fieldValus.length){
			return errCode;
		}
		ContentValues values = new ContentValues();
		
		for(int i=0; i<fieldNames.length; i++){
			values.put(fieldNames[i], fieldValus[i]);
		}
		long mark = getWritableDatabase().insert(table, null, values);
		getWritableDatabase().close();
		return mark;
	}
	
	public long insert(String[] fieldNames, String[] fieldValus){
		return insert(tableName, fieldNames, fieldValus);
	}
	
	public int update(String table, int id, String[] fieldNames, String[] fieldValus){
		ContentValues values = new ContentValues();
		
		for(int i=0; i<fieldNames.length; i++){
			values.put(fieldNames[i], fieldValus[i]);
		}
		int mark = getWritableDatabase().update(table, values, "id="+id, null);
		getWritableDatabase().close();
		return mark;
	}
	public int update(int id, String[] fieldNames, String[] fieldValus){
		return update(tableName, id, fieldNames, fieldValus);
	}
	public Result query(String table){
		Cursor c = getWritableDatabase().query(table, null, null, null, null, null, null);
		return new Result(c);
	}
	
	public Result query(String table, String selection){
		Cursor c = getWritableDatabase().query(table, null, selection, null, null, null, null);
		return new Result(c);
	}
	
	public Result query(String table, String[] columns, String selection){
		Cursor c = getWritableDatabase().query(table, columns, selection, null, null, null, null);
		return new Result(c);
	}
	
	public Result query(String table, String[] columns, String selection, String orderBy){
		Cursor c = getWritableDatabase().query(table, columns, selection, null, null, null, orderBy);
		return new Result(c);
	}
	
	public Result query(){
		return query(tableName);
	}
	
	public Result query(int id){
		return query(tableName, "id="+id);
	}
	
	public Result query(String[] columns, String selection, String orderBy){
		return query(tableName, columns, selection, orderBy);
	}
	
	public void delete(String table, int id){
		String sql = "delete from "+table+" where id = '"+id+"'"; 
		getWritableDatabase().execSQL(sql);
		getWritableDatabase().close();
	}
	public void delete(int id){
		delete(tableName, id);
	}
	
	@Override
	public synchronized void close() {
		super.close();
	}

    public static class Result{
    	private ArrayList<ArrayList<String>> result = null;
    	
    	private int count = 0;
    	private int columnCount = 0;
    	private String[] fieldNames = null;
    	
    	private Result(Cursor c){
    		result = new ArrayList<ArrayList<String>>();
    		count = c.getCount();
    		columnCount = c.getColumnCount();
    		fieldNames = c.getColumnNames();
    		
    		for(c.moveToFirst();!c.isAfterLast(); c.moveToNext()){
    			ArrayList<String> list = new ArrayList<String>();
    			for(int i=0; i<columnCount; i++){
    				list.add(c.getString(i));
    			}
    			result.add(list);
    		}
    	}

		public int getCount() {
			return count;
		}

		public int getColumnCount() {
			return columnCount;
		}
		
		public String[] getFieldNames(){
			return fieldNames;
		}
		
		public int getPosition(String fieldName){
			if(fieldName==null){
				return -1;
			}
			
			for(int i=0; i<fieldNames.length; i++){
				if(fieldName.equals(fieldNames[i]))return i;
			}
			return -1;
		}
		
		public String getString(int row, int cols){
			if(row>count || row<0 || cols>columnCount || cols<0){
				return null;
			}
			return result.get(row).get(cols);
		}
		
		public String getString(int row, String fieldName){
			if(getPosition(fieldName)==-1){
				return null;
			}
			
			return getString(row, getPosition(fieldName));
		}
    	
		public String getString(int row, int cols, String defaultValue){
			String value = getString(row, cols);
			if(value==null){
				return defaultValue;
			}
			return value;
		}
		
		public String getString(int row, String fieldName, String defaultValue){
			String value = getString(row, fieldName);
			if(value==null){
				return defaultValue;
			}
			return value;
		}
		
		public int getInt(int row, String fieldName){
			return Integer.parseInt(getString(row, fieldName));
		}
		public double getDouble(int row, String fieldName){
			return Double.parseDouble(getString(row, fieldName));
		}
    }
}
